set doc off
set verify off
set serveroutput on size 1000000;

declare   
    l_ext_rules			NUMBER;
    l_ext_lines			NUMBER;
    l_ext_usages		NUMBER;
    
	l_imported_rules	NUMBER;
	l_imported_lines	NUMBER;
	l_imported_usages	NUMBER;
	
	l_func_id			VARCHAR2(100):='CAPGLREC_SECURITY';

begin
    select count(1)
    into l_ext_rules    
	from fnd_flex_value_rules 
    where flex_value_set_id IN (select distinct flex_value_set_id
		  					   from fnd_id_flex_segments where 
		 					   id_flex_num = (select chart_of_accounts_id 
			   			                      from gl_sets_of_books 
					  	                      where upper(short_name) = upper('&1'))); 
    
    
    select count(1)
    into l_ext_lines
    from fnd_flex_value_rule_lines l
    where l.flex_value_set_id IN (select distinct flex_value_set_id 
		  					   from fnd_id_flex_segments where 
		 					   id_flex_num = (select chart_of_accounts_id 
			   			                      from gl_sets_of_books 
					  	                      where upper(short_name) = upper('&1')))
	and l.flex_value_rule_id in (select flex_value_rule_id
							 	 from fnd_flex_value_rules 
						   	    where flex_value_set_id IN (select distinct flex_value_set_id 
		  					   from fnd_id_flex_segments where 
		 					   id_flex_num = (select chart_of_accounts_id 
			   			                      from gl_sets_of_books 
					  	                      where upper(short_name) = upper('&1'))));
  
    select count(1) 
    into l_ext_usages
    from fnd_flex_value_rule_usages
    where flex_value_set_id  IN (select distinct flex_value_set_id 
		  					   from fnd_id_flex_segments where 
		 					   id_flex_num = (select chart_of_accounts_id 
			   			                      from gl_sets_of_books 
					  	                      where upper(short_name) = upper('&1')))
    and flex_value_rule_id  in (select flex_value_rule_id
							 	 from fnd_flex_value_rules 
						   	    where flex_value_set_id IN (select distinct flex_value_set_id 
		  					   from fnd_id_flex_segments where 
		 					   id_flex_num = (select chart_of_accounts_id 
			   			                      from gl_sets_of_books 
					  	                      where upper(short_name) = upper('&1'))))	 		;
    
 	SELECT COUNT(1) 
	INTO l_imported_rules
	FROM CRST_FND_FLEX_VALUE_RULES;
	
	SELECT COUNT(1) 
	INTO l_imported_lines
	FROM CRST_FND_FLEX_VALUE_RULE_LINES;
	
	SELECT COUNT(1) 
	INTO l_imported_usages
	FROM CRST_FND_FLEX_VALUE_RULE_USAGE;
	
	dbms_output.put_line('Function: '||l_func_id);		
	dbms_output.put_line('Total Extracted (Rules): '||TO_CHAR(l_ext_rules)) ;
	dbms_output.put_line('Total Imported (Rules):  '||TO_CHAR(l_imported_rules));
	IF l_ext_rules <> l_imported_rules THEN
		dbms_output.put_line('Error: No. of Records do not match!');
	END IF;		
	
	dbms_output.put_line('Total Extracted (Lines): '||TO_CHAR(l_ext_lines)) ;
	dbms_output.put_line('Total Imported (Lines):  '||TO_CHAR(l_imported_lines));
	IF l_ext_lines <> l_imported_lines THEN
		dbms_output.put_line('Error: No. of Records do not match!');
	END IF;		
	
	dbms_output.put_line('Total Extracted (Usages): '||TO_CHAR(l_ext_usages)) ;
	dbms_output.put_line('Total Imported (Usages):  '||TO_CHAR(l_imported_usages));
	IF l_ext_usages <> l_imported_usages THEN
		dbms_output.put_line('Error: No. of Records do not match!');
	END IF;		
						
EXCEPTION
	WHEN OTHERS THEN	
		dbms_output.put_line('Function Error: '||l_func_id);    

end;
/

